package com.suixun.service.count.impl;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.suixun.dao.StdCountBasicMapper;
import com.suixun.entity.StdCountBasic;
import com.suixun.entity.StdCountBasicExample;
import com.suixun.service.count.StdCountBasicService;
import com.suixun.util.ObjectUtil;

import io.jsonwebtoken.lang.Collections;

@Service
public class StdCountBasicServiceImpl implements StdCountBasicService{

    @Resource
    private StdCountBasicMapper stdCountBasicMapper;
    
    @Resource
    private SqlSessionTemplate sqlSessionTemplate;
    @Resource
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public List<Map<String, Object>> listResult(String sql) {

        return jdbcTemplate.queryForList(sql);
        
    }
    
    @Override
    public StdCountBasic getByName(String name) {
        
        StdCountBasicExample example = new StdCountBasicExample();
        example.createCriteria().andNameEqualTo(name);
        
        List<StdCountBasic> list = stdCountBasicMapper.selectByExample(example);
        if(Collections.isEmpty(list)){
            return null;
        }
        return list.get(0);
    }
    
    
    @Override
    public String installSqlSelf(String orgId, String orgType, StdCountBasic countBasic,
            Integer appForm, String beginTime, String endTime) {
        
        
        if(ObjectUtil.isNull(countBasic.getSqlSelect(), countBasic.getSqlFromGuardian(), countBasic.getSqlFromTeacher())){
            return null;
        }
        
        String andSql = "";
        
        if("province".equals(orgType)){
            andSql += " and t.provinceId = '"+orgId+"' ";
        }
        if("city".equals(orgType)){
            andSql += " and t.cityId = '"+orgId+"' ";
        }
        if("town".equals(orgType)){
            andSql += " and t.townId = '"+orgId+"' ";
        }
        if("school".equals(orgType)){
            andSql += " and t.jxtSchoolId = '"+orgId+"' ";
        }
        if("grade".equals(orgType) && appForm == 1){
            andSql += " and t.gradeId = '"+orgId+"' ";
        }else if("grade".equals(orgType) && appForm == 2){
            return null;
        }
        if("class".equals(orgType) && appForm == 1 ){
            andSql += " and t.classId = '"+orgId+"' ";
        }else if("class".equals(orgType) && appForm == 2){
            return null;
        }
        
        if(!ObjectUtil.isNull(countBasic.getSqlAnd())){
            String[] temp = countBasic.getSqlAnd().split("[|]");
            for(String s : temp){
                if(!ObjectUtil.isNull(beginTime)){
                    String s1 = s.replace("{beginTime}", beginTime);
                    if(!s1.contains("{") && !s1.contains("}")){
                        andSql += " " + s1;
                    } 
                }
                if(!ObjectUtil.isNull(endTime)){
                    String s1 = s.replace("{endTime}", endTime);
                    if(!s1.contains("{") && !s1.contains("}")){
                        andSql += " " + s1;
                    } 
                }
                
            }
        }
        
        String fromSql = "";
        if(appForm == 1){
            fromSql = countBasic.getSqlFromGuardian().replace("{andSql}", andSql);
        }else if(appForm == 2){
            fromSql = countBasic.getSqlFromTeacher().replace("{andSql}", andSql);
        }
        
        if(ObjectUtil.isNull(countBasic.getSqlGroup())){
            String selectSql = "select "  + countBasic.getSqlSelect();
            return selectSql + " " + fromSql;
             
        }else{
            String selectSql = "select " + countBasic.getSqlSelect();
            return selectSql + " " + fromSql + " group by " + countBasic.getSqlGroup();
        }
        
    }

    @Override
    public String installSqlByTeacherAndGuardian(String orgId, String orgType, StdCountBasic countBasic,
            String beginTime, String endTime) {
        
    	String guardianSql = this.installSqlSelf(orgId, orgType, countBasic, 1, beginTime, endTime);
        String teacherSql = this.installSqlSelf(orgId, orgType, countBasic, 2, beginTime, endTime);
        
        if(ObjectUtil.isNull(teacherSql)){
            
            if(ObjectUtil.isNull(countBasic.getUnionGroup())){
                
                return "select " + countBasic.getUnionSelect() + " from (" + guardianSql + " ) f ";
            }else{
                
                return "select " + countBasic.getUnionSelect() + " from (" + guardianSql + " ) f group by " + countBasic.getUnionGroup();
            }
        }
        
        if(ObjectUtil.isNull(countBasic.getUnionGroup())){
            
            return "select " + countBasic.getUnionSelect() + " from (" + guardianSql + "  UNION " + teacherSql + " ) f ";
        }else{
            
            return "select " + countBasic.getUnionSelect() + " from (" + guardianSql + "  UNION " + teacherSql + " ) f group by " + countBasic.getUnionGroup();
        }
        
    }
    
    @Override
    public List<StdCountBasic> list() {
        
        StdCountBasicExample example = new StdCountBasicExample();
        
        return stdCountBasicMapper.selectByExample(example);
    }


}
